cls
clear all
local dir "~/Dropbox/Research/Distinction Effect"
cd "`dir'"

use "Data/Intermediates/OLE_2007_2016.dta", clear

merge 1:1 national* using "Data/Intermediates/Pila_2009_2016.dta"
keep if _merge == 3 | _merge == 1
drop _merge

//Graduation Year Postgraduate Degree
tostring fst_g_gradsch, gen(year_gradsch)
replace year_gradsch = substr(year_gradsch, 1, 4)
destring year_gradsch, replace

//Graduation Year Undergraduate Degree
tostring fst_grad, gen(year_grad)
replace year_grad = substr(year_grad, 1, 4)
destring year_grad, replace

//Sample Restriction 
drop if year_grad == .
drop if year_gradsch <= year_grad

destring ole_programcode1, replace
rename ole_programcode1 code_program

gen age_grad = year_grad - year(ole_birthdate)
replace age_grad = . if age_grad < 18 | age_grad > 50

gen birth_year = year(ole_birthdate) if age_grad != .
bys code_program year_grad : egen aux = mode(birth_year)
bys code_program : egen aux2 = mode(birth_year)
egen aux3 = mode(birth_year)
replace birth_year = aux if birth_year == .
replace birth_year = aux2 if birth_year == .
replace birth_year = aux3 if birth_year == .
drop aux* age_grad

//Establishment ID
foreach year of numlist 2009(1)2016 {
	rename pila_firm_id_`year' aux
	egen pila_firm_id_`year' = sieve(aux), keep(n)
	drop aux
	
	gen firm_id_`year' = pila_firm_id_`year' if pila_firm_type_`year' == "NI" | pila_firm_type_`year' == "NIT"
}

//Real Earnings 
local ipc_2007_2007 = 1
local ipc_2007_2008 = 1.076828139
local ipc_2007_2009 = 1.098426412
local ipc_2007_2010 = 1.133137920
local ipc_2007_2011 = 1.175408824
local ipc_2007_2012 = 1.204103672
local ipc_2007_2013 = 1.227398951
local ipc_2007_2014 = 1.272292502
local ipc_2007_2015 = 1.358377044
local ipc_2007_2016 = 1.436439371
foreach year of numlist 2007(1)2016{
	gen oleaux`year' = ole_earnings_`year' if ole_earnings_`year' > 0 & ole_earnings_`year' != .
	gen double earnings_`year' = .
	replace earnings_`year' = oleaux`year'/`ipc_2007_`year''
	drop oleaux`year'
		
	if `year' > 2008 {
	gen pilaaux`year' = pila_earnings_`year' if pila_earnings_`year' > 0 & pila_earnings_`year' != .
	replace earnings_`year' = pilaaux`year'/`ipc_2007_`year'' if earnings_`year' == .
	drop pilaaux`year'
	}
}

keep national_id* year_grad birth_year code_program gender year_gradsch ole_birthdate earnings_???? firm_id_???? pila_firm_type_????
drop earnings_2007 earnings_2008

reshape long earnings_ firm_id_ pila_firm_type_, i(national_id*) j(year)
rename earnings_ earnings
rename firm_id_ firm_id
rename pila_firm_type_ firm_type

drop if firm_id == ""
keep if year >= year_grad
drop if national_id == firm_id

//Keep if there are at least 4 different workers from 2009 to 2016
local num_workers = 9 //4
preserve
	duplicates drop national* firm_id, force
	gen n = 1
	collapse (sum) n, by(firm_id)
	keep if n >= `num_workers'
	drop n
	count
	tempfile Establishments
	save `Establishments'
restore
	
merge m:1 firm_id using `Establishments'
keep if _merge == 3
drop _merge

drop if earnings == .

//Age
gen age = year - birth_year
gen age2 = age^2

//Experience
gen exper = (year - year_grad)
gen exper2 = exper^2

//Graduate Education
gen aux = (year - year_gradsch)
gen grad_educ = 0
replace grad_educ = 1 if aux > 0 & aux != .
drop aux

//Log Earnings
gen ln_earnings = ln(earnings)

//Worker's ID
egen id = group(national_id_type national_id)

//Firms in Within-Industry Productivity Sample
preserve
use "Data/Finals/FirmIndustryRanking.dta", clear
keep firm_id
tempfile Firms
save `Firms', replace
restore

merge m:1 firm_id using `Firms'
keep if _merge == 3
drop _merge

save "Data/Finals/AKM Data.dta", replace

use "Data/Finals/AKM Data.dta", clear

gen female = (gender == "F")

//Crear pct within Industry
merge m:1 firm_id using "Data/Finals/FirmIndustryRanking.dta", keepusing(ciiu ciiu3)
keep if _merge == 3 | _merge == 1
drop _merge

//Model
reghdfe ln_earnings female age age2 grad_educ, absorb(year year_grad firm_fe=firm_id) keepsingletons //cluster(id)

collapse (mean) firm_fe, by(firm_id)
drop if firm_fe == .

sort firm_fe
gen firm_rank = _n
sum firm_rank
replace firm_rank = (firm_rank - r(min))/r(max)

//Crear pct within Industry
merge m:1 firm_id using "Data/Finals/FirmIndustryRanking.dta", keepusing(ciiu ciiu3)
keep if _merge == 3 | _merge == 1
drop _merge

egen double rank_ciiu = rank(firm_rank), by(ciiu3)
egen double max_rank = max(rank_ciiu), by(ciiu3)
egen double min_rank = min(rank_ciiu), by(ciiu3)
replace rank_ciiu= (rank_ciiu-min_rank)/max_rank
drop max_rank min_rank

save "Data/Finals/AKMRanking.dta", replace
